package gestionBulletins.model.persistance.sqliteImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import gestionBulletins.model.Adherent;
import gestionBulletins.model.Section;
import gestionBulletins.model.persistance.DaoAdherent;

/**
 * Implémentation du DAO Adhérent pour le type de persistance SQLite
 * @author Paraita Wohler
 *
 */
public class DaoAdherentSqliteImpl implements DaoAdherent {

	public Connection conn;
	public static String insert = "insert into adherent (etat_civil, nom, " +
								  "nom_d_epouse, prenom1, prenom2, prenom3, date_naissance, " +
								  "adresse, commune, ile, tel_domicile, tel_bureau, vini, " +
								  "profession, religion, situ_famille, nom_conjoint, prenom_conjoint, nb_enfants, " +
								  "sect, num_bureau, num_electeur, has_voted, has_id_card) values " +
								  "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
	public static String reqIdByNomPrenom = "select id from adherent where nom=? and prenom1=?;";
	public static String reqById = "select * from adherent where id=?;";
	public static String update = "update adherent set etat_civil=?, nom=?, nom_d_epouse=?, prenom1=?, prenom2=?, " +
								  "prenom3=?, date_naissance=?, adresse=?, commune=?, ile=?, tel_domicile=?, " +
								  "tel_bureau=?, vini=?, profession=?, religion=?, situ_famille=?, nom_conjoint=?, " +
								  "prenom_conjoint=?, nb_enfants=?, sect=?, num_bureau=?, num_electeur=?, " +
								  "has_voted=?, has_id_card=? where id=?;";
	public static String delete = "delete from adherent where id=?;";
	public static String getall = "select * from adherent;";
	private static String getallInscritsBySection = "select * from adherent where sect=?;";
	
	public DaoAdherentSqliteImpl(Connection conn) {
		this.conn = conn;
	}
	
	@SuppressWarnings("finally")
	@Override
	public Adherent create(Adherent adherent) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(reqIdByNomPrenom);
			ps.setString(1, adherent.getNom());
			ps.setString(2, adherent.getPrenom1());
			rs = ps.executeQuery();
			/* si un adherent avec ce nom et prenom existe deja on retourne null */
			if (rs.next()) {
				adherent = null;
				rs.close();
			}
			else {
				ps.close();
				rs.close();
				ps = conn.prepareStatement(insert);
				ps.setString(1, adherent.getEtatCivil());
				ps.setString(2, adherent.getNom());
				ps.setString(3, adherent.getNomDEpouse());
				ps.setString(4, adherent.getPrenom1());
				ps.setString(5, adherent.getPrenom2());
				ps.setString(6, adherent.getPrenom3());
				ps.setString(7, adherent.getDateNaissanceJour() + "-" + 
								adherent.getDateNaissanceMois() + "-" +
								adherent.getDateNaissanceAnnee());
				ps.setString(8, adherent.getAdresse());
				ps.setString(9, adherent.getCommune());
				ps.setString(10, adherent.getIle());
				ps.setString(11, adherent.getTelDomicile());
				ps.setString(12, adherent.getTelBureau());
				ps.setString(13, adherent.getVini());
				ps.setString(14, adherent.getProfession());
				ps.setString(15, adherent.getReligion());
				ps.setString(16, adherent.getSituationFamiliale());
				ps.setString(17, adherent.getNomConjoint());
				ps.setString(18, adherent.getPrenomConjoint());
				ps.setString(19, adherent.getNbEnfants());
				if (adherent.getSect() == -1) {
					ps.setNull(20, 0);
				}
				else {
					ps.setInt(20, adherent.getSect());
				}
				ps.setString(21, adherent.getNumBureau());
				ps.setString(22, adherent.getNumElecteur());
				ps.setString(23, adherent.getHasVoted());
				ps.setString(24, adherent.getHasCard());
				ps.execute();
				ps.close();
				ps = conn.prepareStatement(reqIdByNomPrenom);
				ps.setString(1, adherent.getNom());
				ps.setString(2, adherent.getPrenom1());
				rs = ps.executeQuery();
				if (rs.next()) {
					adherent.setId(rs.getInt("id"));
				}
				else {
					/* euhhh la y'a un soucis */
					adherent = null;
				}
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return adherent;
		}
	}

	@SuppressWarnings("finally")
	@Override
	public Adherent requestById(int id) {
		Adherent a = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(reqById);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			/* si un adherent avec cet id existe
			 * sinon ca renvoie null
			 * */
			if (rs.next()) {
				String date = rs.getString("date_naissance");
				a = new Adherent(rs.getString("etat_civil"),
								 rs.getString("nom"),
								 rs.getString("prenom1"),
								 date.split("-")[0],
								 date.split("-")[1],
								 date.split("-")[2]);
				a.setNomDEpouse(rs.getString("nom_d_epouse"));
				a.setPrenom2(rs.getString("prenom2"));
				a.setPrenom3(rs.getString("prenom3"));
				a.setAdresse(rs.getString("adresse"));
				a.setCommune(rs.getString("commune"));
				a.setIle(rs.getString("ile"));
				a.setTelDomicile(rs.getString("tel_domicile"));
				a.setTelBureau(rs.getString("tel_bureau"));
				a.setVini(rs.getString("vini"));
				a.setProfession(rs.getString("profession"));
				a.setReligion(rs.getString("religion"));
				a.setSituationFamiliale(rs.getString("situ_famille"));
				a.setNomConjoint(rs.getString("nom_conjoint"));
				a.setPrenomConjoint(rs.getString("prenom_conjoint"));
				a.setNbEnfants(rs.getString("nb_enfants"));
				a.setSect(rs.getInt("sect"));
				a.setNumBureau(rs.getString("num_bureau"));
				a.setNumElecteur(rs.getString("num_electeur"));
				a.setHasCard(rs.getString("has_id_card"));
				a.setHasVoted(rs.getString("has_voted"));
				a.setId(rs.getInt("id"));
			}
			
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (ps != null) {
				try {
					ps.close();
				}
				catch(SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				}
				catch(SQLException e) {
					e.printStackTrace();
				}
			}
			return a;
		}
	}

	@Override
	public void update(Adherent adherent) {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(update);
			ps.setString(1, adherent.getEtatCivil());
			ps.setString(2, adherent.getNom());
			ps.setString(3, adherent.getNomDEpouse());
			ps.setString(4, adherent.getPrenom1());
			ps.setString(5, adherent.getPrenom2());
			ps.setString(6, adherent.getPrenom3());
			ps.setString(7, adherent.getDateNaissanceJour() + "-" + 
							adherent.getDateNaissanceMois() + "-" +
							adherent.getDateNaissanceAnnee());
			ps.setString(8, adherent.getAdresse());
			ps.setString(9, adherent.getCommune());
			ps.setString(10, adherent.getIle());
			ps.setString(11, adherent.getTelDomicile());
			ps.setString(12, adherent.getTelBureau());
			ps.setString(13, adherent.getVini());
			ps.setString(14, adherent.getProfession());
			ps.setString(15, adherent.getReligion());
			ps.setString(16, adherent.getSituationFamiliale());
			ps.setString(17, adherent.getNomConjoint());
			ps.setString(18, adherent.getPrenomConjoint());
			ps.setString(19, adherent.getNbEnfants());
			ps.setInt(20, adherent.getSect());
			ps.setString(21, adherent.getNumBureau());
			ps.setString(22, adherent.getNumElecteur());
			ps.setString(23, adherent.getHasVoted());
			ps.setString(24, adherent.getHasCard());
			ps.setString(25, String.valueOf(adherent.getId()));
			ps.execute();
			ps.close();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	@Override
	public void delete(Adherent adherent) {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(delete);
			ps.setInt(1, adherent.getId());
			ps.execute();
			ps.close();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	@SuppressWarnings("finally")
	@Override
	public int requestIdByNomPrenom(String nom, String prenom) {
		int resultat = -1;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(reqIdByNomPrenom);
			ps.setString(1, nom);
			ps.setString(2, prenom);
			rs = ps.executeQuery();
			resultat = Integer.parseInt(rs.getString(0));
			rs.close();
			ps.close();
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				}
				catch(SQLException e) {
					e.printStackTrace();
				}
			}
			return resultat;
		}
	}

	@SuppressWarnings("finally")
	@Override
	public ArrayList<Adherent> getAll() {
		ArrayList<Adherent> col = new ArrayList<Adherent>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(getall);
			rs = ps.executeQuery();
			while(rs.next()) {
				String date = rs.getString("date_naissance");
				Adherent a = new Adherent(rs.getString("etat_civil"),
								 rs.getString("nom"),
								 rs.getString("prenom1"),
								 date.split("-")[0],
								 date.split("-")[1],
								 date.split("-")[2]);
				a.setNomDEpouse(rs.getString("nom_d_epouse"));
				a.setPrenom2(rs.getString("prenom2"));
				a.setPrenom3(rs.getString("prenom3"));
				a.setAdresse(rs.getString("adresse"));
				a.setCommune(rs.getString("commune"));
				a.setIle(rs.getString("ile"));
				a.setTelDomicile(rs.getString("tel_domicile"));
				a.setTelBureau(rs.getString("tel_bureau"));
				a.setVini(rs.getString("vini"));
				a.setProfession(rs.getString("profession"));
				a.setReligion(rs.getString("religion"));
				a.setSituationFamiliale(rs.getString("situ_famille"));
				a.setNomConjoint(rs.getString("nom_conjoint"));
				a.setPrenomConjoint(rs.getString("prenom_conjoint"));
				a.setNbEnfants(rs.getString("nb_enfants"));
				a.setSect(rs.getInt("sect"));
				a.setNumBureau(rs.getString("num_bureau"));
				a.setNumElecteur(rs.getString("num_electeur"));
				a.setHasCard(rs.getString("has_id_card"));
				a.setHasVoted(rs.getString("has_voted"));
				a.setId(rs.getInt("id"));
				col.add(a);
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (ps != null) {
				try {
					ps.close();
				}
				catch(SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				}
				catch(SQLException e) {
					e.printStackTrace();
				}
			}
			return col;
		}
	}

	@SuppressWarnings("finally")
	@Override
	public Collection<Adherent> getInscritsBySection(Section section) {
		ArrayList<Adherent> col = new ArrayList<Adherent>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(getallInscritsBySection);
			ps.setInt(1, section.getId());
			rs = ps.executeQuery();
			while(rs.next()) {
				String date = rs.getString("date_naissance");
				Adherent a = new Adherent(rs.getString("etat_civil"),
								 rs.getString("nom"),
								 rs.getString("prenom1"),
								 date.split("-")[0],
								 date.split("-")[1],
								 date.split("-")[2]);
				a.setNomDEpouse(rs.getString("nom_d_epouse"));
				a.setPrenom2(rs.getString("prenom2"));
				a.setPrenom3(rs.getString("prenom3"));
				a.setAdresse(rs.getString("adresse"));
				a.setCommune(rs.getString("commune"));
				a.setIle(rs.getString("ile"));
				a.setTelDomicile(rs.getString("tel_domicile"));
				a.setTelBureau(rs.getString("tel_bureau"));
				a.setVini(rs.getString("vini"));
				a.setProfession(rs.getString("profession"));
				a.setReligion(rs.getString("religion"));
				a.setSituationFamiliale(rs.getString("situ_famille"));
				a.setNomConjoint(rs.getString("nom_conjoint"));
				a.setPrenomConjoint(rs.getString("prenom_conjoint"));
				a.setNbEnfants(rs.getString("nb_enfants"));
				a.setSect(rs.getInt("sect"));
				a.setNumBureau(rs.getString("num_bureau"));
				a.setNumElecteur(rs.getString("num_electeur"));
				a.setHasCard(rs.getString("has_id_card"));
				a.setHasVoted(rs.getString("has_voted"));
				a.setId(rs.getInt("id"));
				col.add(a);
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (ps != null) {
				try {
					ps.close();
				}
				catch(SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				}
				catch(SQLException e) {
					e.printStackTrace();
				}
			}
			return col;
		}
	}

}
